﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace PTCScheduler
{
    class Student
    {
        //Student first name.
        private String fname;
        public String FirstName
        {
            get { return fname; }
            set { fname = value; }
        }

        //Student last name.
        private String lname;
        public String LastName
        {
            get { return lname; }
            set { lname = value; }
        }

        //StudentID.
        private int studentID;
        public int StudentID
        {
            get { return studentID; }
            set { studentID = value; }
        }

        //Parent Email.
        private String parentEmail;
        public String ParentEmail
        {
            get { return parentEmail; }
            set { parentEmail = value; }
        }

        //Parent Phone.
        private String parentPhone;
        public String ParentPhone
        {
            get { return parentPhone; }
            set { parentPhone = value; }
        }

        //SQLite Connection
        private SQLiteConnection myConnection;

        //SQLite Transaction
        private SQLiteTransaction transaction;

        //Student Constructor to take in information as arguments.
        public Student(String newfname, String newlname, int newstudentid,
            String newparentemail, String newparentphone)
        {
            //Set datafields to arguments.
            this.FirstName = newfname;
            this.LastName = newlname;
            this.StudentID = newstudentid;
            this.ParentEmail = newparentemail;
            this.ParentPhone = newparentphone;
        }

        //Method to return the student insert string.
        public String insertNewStudentString()
        {
            return @"INSERT INTO students VALUES (" + this.StudentID + ",'" + this.FirstName + "','" +
                this.LastName + "','" + this.ParentEmail + "','" + this.ParentPhone + "')";
        }

        //Method to execute the insert of student into database.
        public void AddNewStudent()
        {
            //Create new sqlite connection
            myConnection = new SQLiteConnection(SystemQueries.dbConnString());

            //Open connection
            myConnection.Open();

            //Set the SQLiteTransaction
            transaction = myConnection.BeginTransaction();

            //Create the SQLite command and set its properties
            SQLiteCommand command = new SQLiteCommand();
            command.Transaction = transaction;
            command.CommandText = this.insertNewStudentString();
            command.ExecuteNonQuery();

            //Commit the transaction
            transaction.Commit();

            //Close connection.
            myConnection.Close();
        }

        //Method to return dataset filled with given student information.
        public DataView dvStudent()
        {
            //Connect to database and open connection.
            myConnection = new SQLiteConnection(SystemQueries.dbConnString());
            myConnection.Open();

            //Retrieve the student query string from the SystemQueries class and set the
            //student id to the desired student ID in studentIDtextbox.
            String sql = SystemQueries.studentInformationQueryString(this.StudentID);

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            SQLiteDataAdapter da = new SQLiteDataAdapter(sql,SystemQueries.dbConnString());

            //Fill the dataset with the results. 
            da.Fill(ds);

            //Close connection.
            myConnection.Close();

            //Return data set.
            return ds.Tables[0].DefaultView;
        }
    }
}
